#include "cdbprovider.h"
#include  <QMessageBox>
#include <QSqlError>
#include <QApplication>
#include <QDebug>


 #ifdef Q_WS_MAC
 const QString rsrcPath = ":/images/mac";
 #else
 const QString rsrcPath = ":/images/win";
 #endif

CDBProvider::CDBProvider(QString path,QWidget * parent):
        QWidget(parent)
{
    db = QSqlDatabase::database("connection");
    tQuery = 0;
    if(!db.isValid()){
        db = QSqlDatabase::addDatabase("QSQLITE","connection");
        if(path.isEmpty())db.setDatabaseName("DB/TestSchool");
        else db.setDatabaseName(path);
        if (!db.open()){
            QMessageBox::warning(this,trUtf8("Алярма"),trUtf8("База не найдена!!!\nDB/TestSchool"));
            qApp->exit();
        }
        //else QMessageBox::information(this,"Welcom","to base DB/TestSchool");
    }
    wait = false;
}

CDBProvider::~CDBProvider(){
    if(tQuery != 0)
        delete tQuery;
}

QList<QTreeWidgetItem *> CDBProvider::getChieldFromDB(int index,bool tasks, int k){
    //wait = true;
    QSqlQuery q(openDB());
    q.clear();
    QString qstr = "select id, name from `group` where id_parent = " + QString::number(index);
    QList<QTreeWidgetItem *> list;
    QSqlQueryModel sqmGroup;
    sqmGroup.setQuery(qstr,db);
    //sqmGroup.
    for(int y = 0;y < sqmGroup.rowCount();++y){
            QTreeWidgetItem *i = new QTreeWidgetItem();
            //int id = ;
            i->setText(0,sqmGroup.record(y).field(1).value().toString());
            i->setText(1,sqmGroup.record(y).field(0).value().toString());
            i->setIcon(0,QIcon(rsrcPath + "/fileopen.png"));
            i->setText(2,"cat");
            i->setText(5,QString::number(k));
            QList<QTreeWidgetItem *> l = getChieldFromDB(sqmGroup.record(y).field(0).value().toInt(),tasks,k+1);
            if(tasks){
                QList<QTreeWidgetItem *> dsa = getTaskChieldFromDB(sqmGroup.record(y).field(0).value().toInt(),k+1);
                foreach(QTreeWidgetItem * jh,dsa)
                    l.append(jh);
                if(!dsa.isEmpty()) i->setText(2,"cat_w_task");
            }
            i->addChildren(l);
            list.append(i);        
    }
    //if(wait)wait = false;
    return list;
}

QList<QTreeWidgetItem *> CDBProvider::getTaskChieldFromDB(int index,int k){
    QSqlQuery q(openDB());
    q.clear();
    QString qstr = "select id, text, order_task from `task` where id_group = " + QString::number(index)+" order by order_task, id";
    QList<QTreeWidgetItem *> list;
    if(q.exec(qstr)){
        int y = 0;
        while(q.next()){
            QTreeWidgetItem *i = new QTreeWidgetItem();
            ++y;
            i->setText(0,trUtf8("Задание №")+QString::number(y));
            i->setText(1,q.value(0).toString());
            i->setText(3,q.value(2).toString());
            i->setData(0,Qt::UserRole,q.value(1));
            i->setData(0,Qt::ToolTipRole,q.value(1));
            i->setText(5,QString::number(k));
            i->setIcon(0,QIcon(rsrcPath + "/filenew.png"));
            i->setText(2,"task");
            list.append(i);
        }
    }
    return list;
}

bool CDBProvider::saveChieldToDB(QList<QTreeWidgetItem *> list,int index){
    if(list.isEmpty()){
//        QMessageBox::information(this,trUtf8("Предупреждение"),trUtf8("список пуст"));
        return true;
    }
    foreach(QTreeWidgetItem * i,list){
        saveChieldToDB(i->takeChildren(),i->text(1).toInt());
        if(!saveTreeWidgetItemToDB(i,index)) return false;
    }
    return true;
}

bool CDBProvider::saveTreeWidgetItemToDB(QTreeWidgetItem *it,int index){
    QString qstr;
    if(it->text(2).indexOf("topCat")!=-1)return false;
    QSqlQuery q(openDB());
    q.clear();
    qstr = it->text(0)+" | "+it->data(0,Qt::UserRole).toString();
    if(it->text(1).isEmpty()){
        if(it->data(0,Qt::UserRole).isNull()){
                q.prepare("INSERT INTO `group`(name,id_parent) VALUES(:name, :idp)");
                q.bindValue(":name",QVariant(it->text(0)));
                q.bindValue(":idp",index);
        }else{
            q.prepare("INSERT INTO `task`(text,id_group,order_task) VALUES(:text, :idg,1)");
            q.bindValue(":text",it->data(0,Qt::UserRole));
            q.bindValue(":idg",index);
        }
        if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при сохранении дерева\n")+qstr+"\n"+q.lastError().text());
            return false;
        }
        it->setText(1,q.lastInsertId().toString());
    }else{
        if(it->data(0,Qt::UserRole).isNull()){
            if(index != it->text(1).toInt()){
                q.prepare("UPDATE `group` SET name = :name, id_parent = :idp  WHERE id = :id");
                q.bindValue(":name",QVariant(it->text(0)));
                q.bindValue(":idp",QString::number(index));
                q.bindValue(":id",it->text(1).toInt());
            }
        }else{
            q.prepare("UPDATE `task` SET text = :text, id_group = :idg WHERE id = :id");
            q.bindValue(":text",it->data(0,Qt::UserRole).toString());
            q.bindValue(":idg",QString::number(index));
            q.bindValue(":id",it->text(1).toInt());
        }
        if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при сохранении дерева\n")+qstr+"\n"+q.lastError().text());
            return false;
        }
    }
    return true;
}

bool CDBProvider::deleteItemFromDB(QTreeWidgetItem * i){

    QSqlQuery q(openDB());
    q.clear();
    if(i->data(0,Qt::UserRole).isNull()){
        q.prepare("DELETE FROM `group` where id= :id");
        q.bindValue(":id",i->text(1).toInt());
    }else{
        q.prepare("DELETE FROM task where id= :id");
        q.bindValue(":id",i->text(1).toInt());
    }
    if(!q.exec()){
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при удалении из дерева\n")+"\n"+q.lastError().text());
        return false;
    }
    return true;
}

QList<MyListItem *> CDBProvider::getAnswer(int taskId){
    QSqlQuery q(openDB());
    q.clear();
     QString qstr = "select id, text from answer where id_tsak = "+QString::number(taskId);
    QList<MyListItem *> list;
    if(q.exec(qstr)){
        int y = 0;
        while(q.next()){
            ++y;
            QString name = trUtf8("Ответ №") + QString::number(y);
            MyListItem *i = new MyListItem(name);
            i->setCheckState(Qt::Unchecked);
            i->data = new QVariant(q.value(1));
            i->id = new QVariant(q.value(0));
            i->id_task = taskId;
            if(rightAnswer(taskId,i->id->toInt()))i->setCheckState(Qt::Checked);
            //i->setData(Qt::UserRole,an);
            list.append(i);
        }
    }else
        QMessageBox::warning(this,trUtf8("Ошибка"),trUtf8("Запрос не верен: ")+qstr+"\n"+q.lastError().text());
    return list;
}

void CDBProvider::saveAnswer(MyListItem *i){
    QSqlQuery q(openDB());
    q.clear();
    if(i->id->toInt()==0){
        q.prepare("INSERT INTO answer(text,id_tsak) VALUES(:text, :id_task)");
        q.bindValue(":text",i->data->toString());
        q.bindValue(":id_task",i->id_task);
        if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при сохранении ответов\n")+"\n"+q.lastError().text());
        }
        i->id = new QVariant(q.lastInsertId());
    }else{
        q.prepare("UPDATE answer SET text = :text WHERE id= :id");
        q.bindValue(":text",i->data->toString());
        q.bindValue(":id",i->id->toInt());
        if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при сохранении ответов\n")+"\n"+q.lastError().text());
        }
    }
    saveRightAnswer(i);
}

bool CDBProvider::deleteItemsFromDB(QList<QTreeWidgetItem *> list){
    if(list.isEmpty())return false;
    foreach(QTreeWidgetItem * i,list){
     deleteItemsFromDB(i->takeChildren());
     deleteItemFromDB(i);
    }
    return true;
}
QSqlDatabase CDBProvider::openDB(){
    if(!db.isValid()) db = QSqlDatabase::database();
    if(!db.open()){
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("База неможет быть открыта")+"\n"+db.lastError().text());
        return QSqlDatabase();
    };
    return db;
}

QString CDBProvider::getTemplateAnswer(int idTask){
    QVariant field(QString("answer"));
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM task WHERE id = :idtask");
    q.bindValue(":idtask",idTask);
    //q.bindValue(":field",field,QSql::InOut);
    if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при открытии ответов\n")+"\n"+q.lastError().text());
    }
    if(q.next()){
        //QMessageBox::information(this,trUtf8("yftghjk"),trUtf8("ghjkl\n")+q.value(2).toString());
    }
    return q.value(2).toString().trimmed();
}

void CDBProvider::saveTemplateAnswer(int idTask,QString text){
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("UPDATE task SET answer = :field WHERE id = :idtask");
    q.bindValue(":idtask",idTask);
    q.bindValue(":field",text);
    if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при обновлении шаблонных ответов\n")+"\n"+q.lastError().text());
    }
    //QMessageBox::information(this,trUtf8("yftghon_checkBox_clickedjk"),q.lastQuery());
}

bool CDBProvider::rightAnswer(int task_id, int answer_id){
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM right_answer WHERE id_tsak = :idtask AND id_answer = :idanswer");
    q.bindValue(":idtask",task_id);
    q.bindValue(":idanswer",answer_id);
    if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при открытии ответов\n")+"\n"+q.lastError().text());
    }
    return q.next();
}

void CDBProvider::saveRightAnswer(MyListItem * i){
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("DELETE FROM right_answer WHERE id_tsak = :idtask AND id_answer = :idanswer");
    q.bindValue(":idtask",i->id_task);
    q.bindValue(":idanswer",i->id->toInt());
    if(!q.exec()){
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при удалении правильных ответов\n")+"\n"+q.lastError().text());
    }
    if(i->checkState() == Qt::Checked){
        q.prepare("INSERT INTO right_answer(id_tsak,id_answer) VALUES(:idtask, :idanswer)");
        q.bindValue(":idtask",i->id_task);
        q.bindValue(":idanswer",i->id->toInt());
        if(!q.exec()){
                QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при сохранении правильных ответов\n")+"\n"+q.lastError().text());
        }
    }
}

bool CDBProvider::exec(QString sql){
    QSqlQuery q(sql,openDB());
    q.clear();
      if(!q.exec()){
          QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса\n")+"\n"+q.lastError().text());
          return false;
      }
     return true;
}

QStringList CDBProvider::getSchools(){
    QSqlQuery q(openDB());
    q.clear();
    QStringList list;
    if(q.exec("select * from school")){
        while(q.next()){
            list.append(q.value(1).toString());
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса школ")+"\n"+q.lastError().text());
    return list;
}

QList <QTreeWidgetItem *> CDBProvider::getGeneral(){    
    QSqlQuery q2(openDB());
    q2.clear();
    QList <QTreeWidgetItem *>list;
    QString qstr = "select distinct `DATE`,s.name,id_school from `general` g\n"
                    "inner join School s on s.id = g.id_School\n"
                    "order by `DATE`,id_School",
            PrevDate;
    QSqlQueryModel sqmSchool;
    sqmSchool.setQuery(qstr,db);
    int PrevSchool;
    QTreeWidgetItem *cd,*cs,*csd = new QTreeWidgetItem(),*c;
    csd->addChildren(getChieldFromDB(0,false,2));

    for(int y = 0; y < sqmSchool.rowCount(); ++y ){
        if(PrevSchool != sqmSchool.record(y).value(2).toInt()||PrevDate != sqmSchool.record(y).value(0).toString()){
             PrevSchool = sqmSchool.record(y).value(2).toInt();
             if(PrevDate != sqmSchool.record(y).value(0).toString()){
                 PrevDate = sqmSchool.record(y).value(0).toString();
                 cd = new QTreeWidgetItem();
                 list .append(cd);
                 cd->setText(0,PrevDate);
                 cd->setText(1,"date");
                 cd->setText(5,QString::number(0));
             }
             cs = csd->clone();
             cd->addChild(cs);
             cs->setText(0,sqmSchool.record(y).value(1).toString());
             cs->setText(1,"school");
             cs->setText(5,QString::number(1));
        }
        q2.clear();
        QString qstr2 = QString("select  gt.id,t.id_group from `general` g "
                        "inner join School s on s.id = g.id_School "
                        "inner join `general_tasks` gt on gt.id_general = g.id "
                        "inner join `general_tasks_task` gtt on gtt.id_general_tasks = gt.id "
                        "inner join `task` t on t.id = gtt.id_task "
                        "where  s.id = %1 and `date`= '%2' "
                        "group by gt.id, t.id_group ").arg(PrevSchool).arg(PrevDate);
        QTreeWidgetItem *ct;
        if(q2.exec(qstr2)){
            int id_group = -1,first = 0;

            while(q2.next()){
                if(id_group != q2.value(1).toInt()){
                    id_group = q2.value(1).toInt();
                    ct = findIdfromGroup(cs,id_group);
                    if(ct == 0) ct = new QTreeWidgetItem();
                    first = 1;
                }
                QTreeWidgetItem * t = new QTreeWidgetItem();
                t->setText(0,q2.value(0).toString());
                t->setText(2,"tester");
                t->setText(5,QString::number(ct->text(5).toInt()+1));
                t->setIcon(0,QIcon(":/new/prefix1/men"));
                ct->addChild(t);
            }
        }else
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса тестов\n")+qstr2+"\n"+q2.lastError().text());
        delnullItem(cs);
    }
    return list;
}

QTreeWidgetItem * CDBProvider::findIdfromGroup(QTreeWidgetItem *root,int id){
    QTreeWidgetItem *n = 0;
    if(root){
        for(int i = 0; i < root->childCount(); ++i){
            n = root->child(i);
            if(n->text(1).toInt()==id) return n;
            n = findIdfromGroup(n,id);
            if(n) return n;
        }
    }
    return n;
}

bool CDBProvider::delnullItem(QTreeWidgetItem *root){
    if(root){
        if(root->text(2) != "tester"){
            for(int i = 0; i < root->childCount(); ++i){
                if(delnullItem(root->child(i))) --i;
            }
            if(root->childCount() == 0){
                delete root;
                return true;
            }else
                root->setText(3,QString::number(root->childCount()));
        }
    }
    return false;
}

QTreeWidgetItem * CDBProvider::getGroupsFroGroup(int id,QTreeWidgetItem *root){
    if(!root) root = new QTreeWidgetItem();
    QTreeWidgetItem *c = new QTreeWidgetItem();
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM `group`  WHERE id = :id");
    q.bindValue(":id",id);
    if(q.exec()){
        q.next();        
        c->setText(0,q.value(1).toString());
        c->setText(1,q.value(0).toString());
        if(q.value(2).toInt()>0){
            root->addChild(getGroupsFroGroup(q.value(2).toInt(),root));
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка списка групп")+"\n"+q.lastError().text());
    return c;
}

QString CDBProvider::getSchool(int id){
    QSqlQuery q(openDB());
    q.clear();
    if(!q.exec("select * from school where id = "+QString::number(id)))
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса школ")+"\n"+q.lastError().text());
    else
        q.next();
    return q.value(1).toString();
}

int CDBProvider::getSchool(QString name){
    QSqlQuery q(openDB());
    q.clear();
    if(!q.exec("select * from school where name = '"+name+"'"))
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса школ")+"\n"+q.lastError().text());
    else
        if(q.next()) return q.value(0).toInt();
        else{
            q.prepare("INSERT INTO school(name) VALUES(:name)");
            q.bindValue(":name",name);
            if(q.exec())
                return q.lastInsertId().toInt();
            else
                QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса школ")+"\n"+q.lastError().text());
        }
    return 0;
}

QList<int> CDBProvider::getListTask(int id){
    QList<int> list;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM `group` WHERE id_parent = :id");
    q.bindValue(":id",id);
    if(q.exec()){
        while(q.next()){
            list+=getListTask(q.value(0).toInt());
        }
        if(list.isEmpty()){
            list = getIdListTask(q.value(0).toInt());
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий из группы")+"\n"+q.lastError().text());
    return list;
}

QList<int> CDBProvider::getIdListTask(int id){
    QList<int> list;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM task WHERE id_group = :id");
    q.bindValue(":id",id);
    if(q.exec()){
        while(q.next()){
            list.append(q.value(0).toInt());
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+"\n"+q.lastError().text());
    return list;
}

int CDBProvider::insertGeneral(QString date,QString nameGroup,int idSchool){
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("INSERT INTO general(date,NameGroup,id_School) VALUES(:date, :nameGroup, :idSchool)");
    q.bindValue(":date",date);
    q.bindValue(":nameGroup",nameGroup);
    q.bindValue(":idSchool",idSchool);
    if(!q.exec())
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки тестов")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
    return q.lastInsertId().toInt();
}

int CDBProvider::insertGeneralTasks(int id){
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("INSERT INTO general_tasks(id_general) VALUES(:id)");
    q.bindValue(":id",id);
    //q.bindValue(":id_task",idTask);
    if(!q.exec())
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
    return q.lastInsertId().toInt();
}

void CDBProvider::insertGeneralTasksTask(int id,QList<int> list){
    QSqlQuery q(openDB());
    q.clear();
    QString qstr;
    if(!q.exec("BEGIN TRANSACTION"))
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
    foreach(int idTask, list){
        qstr = "INSERT INTO general_tasks_task(id_general_tasks,id_task) VALUES("+QString::number(id)+","+QString::number(idTask)+");\n";
        if(!q.exec(qstr))
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
    }
if(!q.exec("COMMIT"))
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
    qstr = qstr.left(qstr.count()-1);

}

QList<QListWidgetItem *> CDBProvider::getItemsForTasks(int id){
    QList<QListWidgetItem *> list;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM general_tasks WHERE id_general = :id");
    q.bindValue(":id",id);
    if(q.exec()){
        while(q.next()){
            QListWidgetItem *i = new QListWidgetItem();
            i->setText(q.value(0).toString());
            i->setData(Qt::UserRole,id);
            list.append(i);
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка id запроса заданий")+"\n"+q.lastError().text());
    return list;
}

QList<QTreeWidgetItem *> CDBProvider::getTItemsForTasks(int id){
    QList<QTreeWidgetItem *> list;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT * FROM general_tasks WHERE id_general = :id");
    q.bindValue(":id",id);
    if(q.exec()){
        while(q.next()){
            QTreeWidgetItem *i = new QTreeWidgetItem();
            i->setText(0,q.value(0).toString());
            i->setText(1,"tester");
            list.append(i);
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка id запроса заданий")+"\n"+q.lastError().text());
    return list;
}

QStringList CDBProvider::getTaskForTasks(int id){
    QStringList list;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("SELECT text FROM general_tasks_task,task WHERE id_general_tasks = :id AND id_task = task.id");
    q.bindValue(":id",id);
    if(q.exec()){
        while(q.next()){
            list.append(q.value(0).toString());
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+"\n"+q.lastError().text());
    return list;
}

void CDBProvider::deleteGeneralTasks(int id){
    QSqlQuery q(openDB()),q2(QSqlDatabase::cloneDatabase(openDB(),"cons"));
    q.clear();
    if(q.exec("select * from general_tasks where id ="+QString::number(id))){
        while(q.next()){
            QString idTask =q.value(0).toString();
            q2.exec("delete from general_tasks_task where id_general_tasks="+idTask);
        }
    }
    q.clear();
    if(!q.exec("delete from general_tasks where id_general="+QString::number(id))) QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при удалении из сгенерированной группы")+"\n"+q.lastError().text());
    q.clear();
    if(!q.exec("delete from general where id="+QString::number(id))) QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка при удалении сгенерированной группы")+"\n"+q.lastError().text());
}

void CDBProvider::renameSchool(QString name,QString newname){
    QStringList list;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("UPDATE school set name= :newname where name=:name");
    q.bindValue(":name",name);
    q.bindValue(":newname",newname);
    if(!q.exec())
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка обновления школ")+"\n"+q.lastError().text());
}
QMap<QString,QString> CDBProvider::getDataForTasks(int idTask){
    QMap<QString,QString> list;
    QSqlQuery q(openDB());
    q.clear();
    if(q.exec("select school.name,date,namegroup,answer,id_group from school "
              "inner join general on school.id=general.id_school "
              "inner join general_tasks on general.id=general_tasks.id_general "
              "inner join general_tasks_task on general_tasks.id =general_tasks_task.id_general_tasks "
              "inner join task on general_tasks_task.id_task=task.id "
              "where general_tasks.id="+QString::number(idTask)+" order by answer desc limit 1")){
            q.next();
            list.insert("SCHOOL",q.value(0).toString());
            list.insert("DATE",q.value(1).toString());
            list.insert("GROUP",q.value(2).toString());
            list.insert("DISABLE_B",q.value(3).toString());
            int id = q.value(4).toInt();
            q.clear();
            if(q.exec("select a.name,b.name,c.name from `group` a "
                      "inner join`group` b on a.id=b.id_parent "
                      "inner join `group` c on b.id=c.id_parent "
                      "where c.id ="+QString::number(id)+" limit 1")){
                q.next();
                list.insert("CLASS",q.value(0).toString());
                list.insert("DISCIPLINE",q.value(1).toString());
                list.insert("CATALOG",q.value(2).toString());
            }else
                QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса данных для заданий")+"\n"+q.lastError().text());

    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса данных для заданий")+"\n"+q.lastError().text());
    return list;
}

void CDBProvider::insertAnswerTester(Blank b){
    QString str = QString("select answer.id_tsak,answer.id from answer\n"
                          "inner join general_tasks_task on answer.id_tsak = general_tasks_task.id_task\n"
                          "inner join task on task.id = answer.id_tsak\n"
                          "where general_tasks_task.id_general_tasks =%1 order by task.order_task,answer.id_tsak, answer.id").arg(b.shifr);
    QSqlQuery q(openDB());
    QSqlQuery q2(openDB());
    q.clear();
    q2.clear();
    if(!q2.exec(QString("delete from general_task_answers where id_general_task = %1").arg(b.shifr)))
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки ответов тестируемых А")+"\n"+q2.lastError().text());
    if(!q2.exec("BEGIN TRANSACTION"))
        QMessageBox::critical(this,trUtf8("Ошибка BEGIN TRANSACTION"),trUtf8("Ошибка вставки ответов тестируемых А")+"\n"+q2.lastError().text());
    if(q.exec(str)){
        QString prev_task_id("");
        int i=-1,j=0;

        while(q.next() && i<25){
            QString task_id = q.value(0).toString();            
            QString id_answer = q.value(1).toString();

            if(task_id == prev_task_id){
                ++j;
            }else{
                j=0;
                ++i;
                prev_task_id = task_id;
            }

                if(b.a[i][j]){
                    if(!q2.exec(QString("INSERT INTO general_task_answers(id_general_task,id_answer,id_task) VALUES(%1,%2,%3)")
                                        .arg(b.shifr)
                                        .arg(id_answer)
                                        .arg(task_id)))
                        QMessageBox::critical(this,trUtf8("Ошибка INSERT"),trUtf8("Ошибка вставки ответов тестируемых A")+"\n"+q2.lastError().text());
                }             
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка select"),trUtf8("Ошибка запроса данных для ответов тестирования А")+
                              "\n"+str+"\n"+q.lastError().text());
   if(!q2.exec("COMMIT"))
       QMessageBox::critical(this,trUtf8("Ошибка COMMIT"),trUtf8("Ошибка вставки ответов тестируемых А")+"\n"+q.lastError().text());

   str = QString("select  task.id,task.answer from task \n"
         "inner join general_tasks_task on task.id = general_tasks_task.id_task\n"
         "where general_tasks_task.id_general_tasks = %1 and trim(task.answer) <>\"\" order by task.order_task, task.id").arg(b.shifr);
   if(!q2.exec("BEGIN TRANSACTION"))
        QMessageBox::critical(this,trUtf8("Ошибка BEGIN TRANSACTION"),trUtf8("Ошибка вставки ответов тестируемых Б")+"\n"+q2.lastError().text());

   if(q.exec(str)){
       int i = 0;
        while(q.next()){            
            if(!q2.exec(QString("INSERT INTO general_task_answers(id_general_task,id_task,answer) VALUES(%1,%2,'%3')")
                                .arg(b.shifr)
                                .arg(q.value(0).toString())
                                .arg(i<10?b.b[i++]:0)))
                QMessageBox::critical(this,trUtf8("Ошибка INSERT"),trUtf8("Ошибка вставки ответов тестируемых Б")+"\n"+q2.lastError().text());
        }
    }else
        QMessageBox::critical(this,trUtf8("Ошибка select"),trUtf8("Ошибка запроса данных для ответов тестирования Б")+
                              "\n"+str+"\n"+q.lastError().text());
    if(!q2.exec("COMMIT"))
        QMessageBox::critical(this,trUtf8("Ошибка COMMIT"),trUtf8("Ошибка вставки ответов тестируемых Б")+"\n"+q2.lastError().text());
}

QString CDBProvider::getReportTotal(QStringList listId){
    if(listId.isEmpty()){
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Нет тестов для печати"));
        return "";
    }
    QStringList listIdTesters;
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("select distinct(general_tasks.id) from general_tasks inner join general_task_answers a on a.id_general_task = general_tasks.id "
              "where  id_general in ("+listId.join(",")+") order by 1");
    if(q.exec()){
        while(q.next()){
            listIdTesters.append(q.value(0).toString());
        }
     }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса тестов")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
    return getReportTotalToTester(listIdTesters);
}

QString CDBProvider::getReportTotalToTester(QStringList listId){
    QString head,
            body,
            total,
            percentTotal;
    QMap<int,QString> body_lines;
    int a = 0,b = 0,i = 0,column = 0;

    QSqlQuery q(openDB());
    q.clear();
    q.prepare("select task.id,trim(task.answer) from general_tasks_task "
              "inner join general_tasks on general_tasks.id = id_general_tasks "
              "inner join task on task.id=general_tasks_task.id_task "
              "where id_general_tasks ="+(listId.isEmpty()?"0":listId[0])+" "
              "group by id_task order by order_task,id_task");
    if(q.exec()){
        a = 1;b = 1;
        while(q.next()){               
            head += tr("<th bgcolor=#FFFFFF>%1%2</th>").arg(q.value(1).toString().isEmpty()?"A":"B").arg(q.value(1).toString().isEmpty()?a++:b++);
            column++;
        }                
        head =  trUtf8("<tr><th bgcolor=#FFFFFF>Шифр</th>%1<th bgcolor=#FFFFFF>Прав.</th></tr>").arg(head);
    }else
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+"\n"+q.lastError().text());

    double t[column];
    for(int c =0; c < column; ++c)
        t[c]=0;

    foreach(QString id,listId){
        QString tempBody;
        q.clear();
        q.prepare("select r.ci,q.an,r.an from (select id_general_tasks,task.id id_t,task.answer an from general_tasks_task "
                  "inner join general_tasks on general_tasks.id = id_general_tasks "
                  "inner join task on task.id=general_tasks_task.id_task "
                  "where general_tasks_task.id_general_tasks="+id+" "
                  "group by id_task) q left join "
                  "(select a.id_task id_t , sum(b.c) ci,a.answer an from general_task_answers a "
                  "left join (select a.*,(10/b.a)c from right_answer a "
                  "inner join (select right_answer.*,count(*) as a   from right_answer "
                  "group by id_tsak ) b on a.id_tsak = b.id_tsak) b on a.id_answer = b.id_answer "
                  "where a.id_general_task ="+id+" group by id_task) r "
                  "on r.id_t =q.id_t");
        //QMessageBox::information(this,"dsaf",q.lastQuery());
        i = 0;a = 0;
        if(q.exec()){
            while(q.next()){
                QString ra = q.value(1).toString().toLower().trimmed(),
                        qa = q.value(2).toString().toLower().trimmed();
                int ca = ra == qa;
                b = (q.value(1).toString().isEmpty()?(q.value(0).toInt()>9?1:0):ca);
                i += b;
                tempBody += tr("<td bgcolor=#FFFFFF align=center>%1</td>").arg(b);
                t[a] += b;
                a++;
            }
            tempBody =  trUtf8("<tr><td bgcolor=#FFFFFF>%1</td>%2<td bgcolor=#FFFFFF align=center>%3</td></tr>").arg(id).arg(tempBody).arg(i);
    //        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+QString("\n%1 %2").arg(id).arg(i>0));
        }else
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+"\n"+q.lastQuery()+"\n"+q.lastError().text());

        //if(i>0)
            body_lines.insertMulti(i,tempBody);
    }    

    QMap<int,QString>::iterator iter = body_lines.end();
    do{
        --iter;
        body+=iter.value()+"\n";

    }while(iter != body_lines.begin());

    int s=0;
    double countTester = body_lines.count();
    for(int c =0; c < column; ++c){
        total += tr("<td bgcolor=#FFFFFF align=center>%1</td>").arg(t[c]);
        s += (int)t[c];
    }
    total = trUtf8("<tr><td bgcolor=#FFFFFF>Итого:<br>%3 уч.</td>%1<td bgcolor=#FFFFFF align=center>%2</td></tr>\n").arg(total).arg(s).arg(countTester);

    double dd;
    for(int c =0; c < column; ++c){
        dd = t[c]/countTester;
        percentTotal += tr("<td bgcolor=#FFFFFF align=center>%1</td>").arg(100-(dd*100), 0, 'f', 2);
    }    

    percentTotal = trUtf8("<tr><td bgcolor=#FFFFFF>Трудность<br>\nзадания:\%</td>%1<td bgcolor=#FFFFFF align=center>&nbsp;</td></tr>\n").arg(percentTotal);

    return QString("<table bgcolor=#333300 width=100% ><thead>%1</thead><tbody>%2%3</tbody><tfoot>%4</tfoot></table>").arg(head).arg(body).arg(total).arg(percentTotal);
}

QList<QTreeWidgetItem *> CDBProvider::getTestFromDB(int index){
    QSqlQuery q(openDB());
    q.clear();
    QString qstr = "select id, name from `group` where id_parent = " + QString::number(index);
    QList<QTreeWidgetItem *> list;
    if(q.exec(qstr)){
        int y=0;
        while(q.next()){
            ++y;
            QTreeWidgetItem *i = new QTreeWidgetItem();
            i->setText(0,q.value(1).toString());
            i->setText(1,q.value(0).toString());
            i->setIcon(0,QIcon(rsrcPath + "/fileopen.png"));
            i->setText(2,"cat");
            QList<QTreeWidgetItem *> l = getTestFromDB(q.value(0).toInt());
            if(l.isEmpty()){
                l = getTestChieldFromDB(q.value(0).toInt());
                if(!l.isEmpty()) i->setText(2,"cat_w_task");
            }
            i->addChildren(l);
            list.append(i);
        }
    }else
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+"\n"+q.lastError().text());    
    return list;
}

QList<QTreeWidgetItem *> CDBProvider::getTestChieldFromDB(int index){
    QSqlQuery q(openDB());
    q.clear();
    if(tQuery == 0)
    {   
        tQuery = new QSqlQueryModel();
    }   

    QString qstr = "select distinct ge.date,s.name,ge.id, ge.namegroup, a.id_group from task a "
                      "inner join general_task_answers ta on ta.id_task= a.id "
                      "inner join general_tasks t on t.id =ta.id_general_task "
                      "inner join \"general\" ge on ge.id = t.id_general "
                      "inner join school s on s.id = ge.id_school "
                      "where a.id_group = " + QString::number(index);

    tQuery->setQuery(qstr,db);

    QList<QTreeWidgetItem *> list;
    int y =0;
    for(; y < tQuery->rowCount(); ++y ){

            QTreeWidgetItem *i = new QTreeWidgetItem();

            i->setText(0,tQuery->record(y).value(0).toString()+" "+tQuery->record(y).value(1).toString()+trUtf8(" класс:")+tQuery->record(y).value(3).toString());
            i->setText(1,tQuery->record(y).value(2).toString());
            i->setText(2,"test");
            i->setText(3,tQuery->record(y).value(3).toString());
            list.append(i);
    }

    return list;
}

QString CDBProvider::getStrinFromTree(int id){
    QSqlQuery q(openDB());
    q.clear();
    QString qstr ="select g.name,g1.name,g2.name from task a "
                  "inner join \"group\" g on a.id_group = g.id "
                  "inner join \"group\" g1 on g1.id = g.id_parent "
                  "inner join \"group\" g2 on g2.id = g1.id_parent "
                  "inner join general_task_answers ta on ta.id_task= a.id  "
                  "inner join general_tasks t on t.id =ta.id_general_task "
                  "inner join \"general\" ge on ge.id = t.id_general "
            //      "inner join school s on s.id = ge.id_school "
                  "where ge.id="+QString::number(id)+" limit 1",
                 result;

    if(q.exec(qstr)){                
        q.next();
        result += q.value(2).toString()+"\\"+q.value(1).toString()+"\\"+q.value(0).toString();
    }else
            QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
    return result;
}

void CDBProvider::deleteResult(int id){
    QSqlQuery q(openDB());
    q.clear();
    QString qstr ="delete from general_task_answers "
                  "where id="+QString::number(id);
    if(!q.exec(qstr))
       QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка удаления загруженых отвтеов")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
}

void CDBProvider::deleteAnswerFromDB(int id){
    QSqlQuery q(openDB());
    q.clear();
    QString qstr ="delete from answer "
                  "where id="+QString::number(id);
    if(!q.exec(qstr))
       QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка удаления отвтеов")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
}

void CDBProvider::taskSetOrder(int id, int order){
    QSqlQuery q(openDB());
    q.clear();
    q.prepare("UPDATE `task` SET order_task = :order  WHERE id = :id");
    q.bindValue(":order",order);
    q.bindValue(":id",id);
    if(!q.exec())
       QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка установки порядка")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
}

QMap <int, QMap<int,bool> > CDBProvider::getTasksTester(int id){
    QMap <int, QMap<int,bool> > tasks;
    QSqlQuery q(openDB()),q2(openDB());
    q.clear();q2.clear();
    q.prepare(  "select a.id_task,k.id from general_tasks_task a "
                "inner join answer k on k.id_tsak = a.id_task "
                "inner join task t on k.id_tsak= t.id "
                "where a.id_general_tasks = :id order by t.order_task,id_task");
    q.bindValue(":id",id);
    if(q.exec()){
        int prev_id = 0;        
        QList <int>answersTestForTask;
        while(q.next()){
            QMap<int,bool> answers = tasks.take(q.value(0).toInt());            
            if(prev_id != q.value(0).toInt()){
                answersTestForTask.clear();
                prev_id = q.value(0).toInt();
                q2.prepare( "select a.id_answer from general_task_answers a "
                            "where a.id_task = id_task and a.id_general_task= :id_gen");
                q2.bindValue(":id_task",q.value(0).toInt());
                q2.bindValue(":id_gen",id);                
                if(q2.exec()){
                    while(q2.next()){                        
                        answersTestForTask.append(q2.value(0).toInt());
                    }
                }else
                    QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса ответов тестируемого.")+"\n"+q2.lastQuery()+"\n"+q2.lastError().text());
                q2.clear();
            }
            bool c =false;
            foreach(int a, answersTestForTask){                
                if(c = (q.value(1).toInt() == a)) break;
            }
            answers.insert(q.value(1).toInt(),c);
            tasks.insert(q.value(0).toInt(),answers);
        }
    }else
       QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий тестируемого.")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
    return tasks;
}

QMap <int, QString > CDBProvider::getTasksBTester(int id){
    QMap <int, QString > tasks;
    QSqlQuery q(openDB()),q2(openDB());
    q.clear();q2.clear();
    q.prepare( "select t.id, a.answer from task t "
               "inner join general_tasks_task gt on gt.id_task = t.id "
               "left outer join general_task_answers a on a.id_task= t.id and gt.id_general_tasks = a.id_general_task "
               "where gt.id_general_tasks= :id and trim(t.answer) <> \"\" order by t.order_task,gt.id_task");
    q.bindValue(":id",id);
    if(q.exec()){        
        while(q.next()){            
            tasks.insert(q.value(0).toInt(),q.value(1).toString());
        }
    }else
       QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка запроса заданий тестируемого.")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
    return tasks;
}

void CDBProvider::insertAnswerTesterA(int id,QList <int> id_answer){
    QSqlQuery q(openDB()),q2(openDB());
        q.clear();        
    if(!q.exec("BEGIN TRANSACTION"))
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
        q.prepare("delete from general_task_answers WHERE id_general_task = :id and not id_answer is null");        
        q.bindValue(":id",id);
        if(!q.exec())
           QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка установки порядка")+"\n"+q.lastQuery()+"\n"+q.lastError().text());

   foreach(int id_ans, id_answer){
        q.clear();
        q2.clear();
        q2.prepare("select id_tsak from answer inner join task on task.id = answer.id_tsak where answer.id = "+QString::number(id_ans)+" order by task.order_task");
        if(!q2.exec())
           QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка установки порядка")+"\n"+q2.lastQuery()+"\n"+q2.lastError().text());
        q2.next();
        int id_task = q2.value(0).toInt();
        q.prepare("insert into general_task_answers(id_general_task,id_answer,id_task) values(:id_general_task,:id_answer,:id_task)");
        q.bindValue(":id_task",id_task);
        q.bindValue(":id_answer",id_ans);
        q.bindValue(":id_general_task",id);
        if(!q.exec())
           QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка установки порядка")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
    }
   q2.clear();
   q.clear();
   if(!q.exec("COMMIT")){
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
        q.exec("ROLLBACK");
            //  QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
    }
}

void CDBProvider::insertAnswerTesterB(int id,QMap <int,QString > answers){
    QSqlQuery q(openDB()),q2(openDB());
        q.clear();
    if(!q.exec("BEGIN TRANSACTION"))
        QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка вставки заданий для тестов")+"\n"+q.lastError().text());
        q.prepare("delete from general_task_answers WHERE id_general_task = :id and id_answer is null");
        q.bindValue(":id",id);
        if(!q.exec())
           QMessageBox::critical(this,trUtf8("Ошибка"),trUtf8("Ошибка установки порядка")+"\n"+q.lastQuery()+"\n"+q.lastError().text());
       QMapIterator <int,QString > answer(answers);
       while (answer.hasNext()) {
         answer.next();
       if(!q2.exec(QString("INSERT INTO general_task_answers(id_general_task,id_task,answer) VALUES(%1,%2,'%3')")
                                    .arg(id)
                                    .arg(answer.key())
                                    .arg(answer.value())))
            QMessageBox::critical(this,trUtf8("Ошибка INSERT"),trUtf8("Ошибка вставки ответов тестируемых Б")+"\n"+q2.lastError().text());
    }
    if(!q2.exec("COMMIT"))
        QMessageBox::critical(this,trUtf8("Ошибка COMMIT"),trUtf8("Ошибка вставки ответов тестируемых Б")+"\n"+q2.lastError().text());
};
